package mobi.weq.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import mobi.weq.db.Database;
import mobi.weq.model.Observation;
import mobi.weq.model.UserObservation;

public class UserObservationDao {
	public synchronized int addObservations(UserObservation ob)
			throws SQLException {
		Connection c = Database.getConnection();
		String query = "insert into UserObservation(id,city,toleranceIndex,comments,latitude,longitude,timestamp) values(?,?,?,?,?,?,?,?)";
		PreparedStatement ps = c.prepareStatement(query);
		ps.setString(1, ob.getId());
		ps.setString(2, ob.getCity());
		ps.setInt(3, ob.getToleranceLevel());
		ps.setString(4, ob.getComments());
		ps.setDouble(5, ob.getLatitude());
		ps.setDouble(6, ob.getLongitude());
		Date d = ob.getTimestamp();
		if (d != null)
			ps.setTimestamp(7, new java.sql.Timestamp(d.getTime()));
		else
			ps.setDate(7, null);

		return ps.executeUpdate();

	}

	public List<UserObservation> getAllObservations() throws SQLException {
		Connection c = Database.getConnection();
		String query = "select * from UserObservation";
		PreparedStatement ps = c.prepareStatement(query);

		ResultSet rs = ps.executeQuery();
		List<UserObservation> observations = new ArrayList<UserObservation>();

		while (rs.next()) {
			UserObservation ob = new UserObservation();
			ob.setId(rs.getString(1));
			ob.setCity(rs.getString(8));
			ob.setToleranceLevel(rs.getInt(3));
			ob.setComments(rs.getString(10));
			ob.setLatitude(rs.getDouble(13));
			ob.setLongitude(rs.getDouble(14));
			ob.setTimestamp(rs.getTimestamp(16));
			observations.add(ob);
		}
		return observations;
	}

	public synchronized int deleteObservation(Observation ob)
			throws SQLException {
		Connection cn = Database.getConnection();
		String sql = "delete from UserObservation where ?=?";
		PreparedStatement ps = cn.prepareStatement(sql);
		return ps.executeUpdate();
	}
}
